const connection = require("../app/database");

class articleService {
  // 发布文章
  async releaseArticleService(articleParameter) {
    const {
      uid,
      view_counts,
      title,
      summary,
      body,
      category_id,
      type,
      cover1,
      cover2,
      cover3,
    } = articleParameter;
    const statement = `
    INSERT INTO article (uid,title,summary,body,category_id,type,view_counts,cover1,cover2,
      cover3) VALUES 
    (?,?,?,?,?,?,?,?,?,?)
    `;
    try {
      const [result] = await connection.execute(statement, [
        uid,
        title,
        summary,
        body,
        category_id,
        type,
        view_counts,
        cover1,
        cover2,
        cover3,
      ]);
      return result;
    } catch (error) {
      console.log(error);
    }
  }

  // 收藏文章
  async saveArticleService(articleParameter) {
    const { uid, article_id, article_uid } = articleParameter;
    const statement = `
    INSERT INTO article_save (uid,article_id, article_uid) VALUES 
    (?,?,?)
    `;
    try {
      const [result] = await connection.execute(statement, [
        uid,
        article_id,
        article_uid,
      ]);
      return result;
    } catch (error) {
      console.log(error);
    }
  }

  // 取消收藏文章
  async cancelSaveArticleService(articleParameter) {
    const { uid, article_id, article_uid } = articleParameter;
    const statement = `
    DELETE FROM article_save WHERE uid = ? AND article_id = ? AND article_uid = ?;
    `;
    try {
      const [result] = await connection.execute(statement, [
        uid,
        article_id,
        article_uid,
      ]);
      return result;
    } catch (error) {
      console.log(error);
    }
  }

  // 点赞文章
  async supportArticleService(articleParameter) {
    const { uid, article_id, article_uid } = articleParameter;
    const statement = `
    INSERT INTO article_support (uid,article_id, article_uid) VALUES 
    (?,?,?)
    `;
    try {
      const [result] = await connection.execute(statement, [
        uid,
        article_id,
        article_uid,
      ]);
      return result;
    } catch (error) {
      console.log(error);
    }
  }

  // 取消点赞文章
  async cancelSupportArticleService(articleParameter) {
    const { uid, article_id, article_uid } = articleParameter;
    const statement = `
    DELETE FROM article_support WHERE uid = ? AND article_id = ? AND article_uid = ?;
    `;
    try {
      const [result] = await connection.execute(statement, [
        uid,
        article_id,
        article_uid,
      ]);
      return result;
    } catch (error) {
      console.log(error);
    }
  }

  // 评论文章
  async commentArticleService(parameter) {
    const { user_id, content, comment_id, article_id } = parameter;
    const statement = `
      INSERT INTO article_comment (user_id,content,article_id,comment_id) VALUES (?,?,?,?)
    `;
    try {
      const [result] = await connection.execute(statement, [
        user_id,
        content,
        article_id,
        comment_id,
      ]);
      return result;
    } catch (error) {
      console.log(error);
    }
  }

  // 获取文章
  async getArticleService(parameter) {
    const field = {
      lists: [],
      total: 0,
    };

    try {
      const statement1 = "SELECT COUNT(*) as total FROM article";
      const [totalResult] = await connection.execute(statement1);
      field.total = totalResult[0].total;

      if (!totalResult[0].total) {
        return field;
      }

      const { offset, limit } = parameter;
      const statement2 = `
      SELECT 
      a.id id, a.title title,a.summary summary,a.body body, 
			a.type type,a.weight weight,a.view_counts view_counts,
			CASE
				WHEN a.cover3 IS NOT NULL AND a.cover3 != '' THEN JSON_ARRAY(a.cover1, a.cover2, a.cover3)
				WHEN a.cover2 IS NOT NULL AND a.cover2 != '' THEN JSON_ARRAY(a.cover1, a.cover2)
				WHEN a.cover1 IS NOT NULL AND a.cover1 != '' THEN JSON_ARRAY(a.cover1)
				ELSE JSON_ARRAY()
			END AS covers,
			a.createAt createTime, a.updateAt updateTime,
			a.audit_reason audit_reason,a.audit_time audit_time,
			JSON_OBJECT('id',u.id,'name',u.name,'createTime',u.createAt,'avatar',u.avatar,'account',u.account,
			'fans_counts', (SELECT COUNT(*) FROM attention WHERE attention_uid = u.id),
			'attention_counts',(SELECT COUNT(*) FROM attention WHERE uid = u.id),
			'support_counts',(SELECT COUNT(*) FROM article_support WHERE article_uid = u.id)) AS user,
			JSON_OBJECT('id',c.id,'label',c.category_label,'name',c.category_name,'avatar',c.category_avatar,'account',c.article_count) AS category,
			(SELECT COUNT(*) FROM article_save WHERE article_id = a.id) AS save_counts,
			(SELECT COUNT(*) FROM article_support WHERE article_id = a.id) AS support_counts,
			(SELECT 
					JSON_ARRAYAGG(
							JSON_OBJECT(
									'id', ac.id,
									'content', ac.content,'commentId',ac.comment_id,
									'user', JSON_OBJECT('id', u.id, 'name', u.name,'avatar',u.avatar,'account',u.account),
									'createAt', ac.createAt,
									'updateAt', ac.updateAt
							)
					)
				FROM article_comment ac
				LEFT JOIN user u ON u.id = ac.user_id
				WHERE ac.article_id = a.id) AS comments
      FROM article a
      LEFT JOIN user u ON u.id = a.uid
			LEFT JOIN category c ON c.id = a.category_id
			WHERE a.is_delete = 0 AND a.audit_state = 2
			ORDER BY weight DESC
         LIMIT ? OFFSET ?
      `;
      const [result] = await connection.execute(statement2, [limit, offset]);
      field.lists = result;

      return field;
    } catch (error) {
      console.log(error);
    }
  }

  // 通过id获取文章
  async getArticleByIdService(articleId) {
    try {
      const statement2 = `
      SELECT 
      a.id id, a.title title,a.summary summary,a.body body, 
			a.type type,a.weight weight,a.view_counts view_counts,
			CASE
				WHEN a.cover3 IS NOT NULL AND a.cover3 != '' THEN JSON_ARRAY(a.cover1, a.cover2, a.cover3)
				WHEN a.cover2 IS NOT NULL AND a.cover2 != '' THEN JSON_ARRAY(a.cover1, a.cover2)
				WHEN a.cover1 IS NOT NULL AND a.cover1 != '' THEN JSON_ARRAY(a.cover1)
				ELSE JSON_ARRAY()
			END AS covers,
			a.createAt createTime, a.updateAt updateTime,
			a.audit_reason audit_reason,a.audit_time audit_time,
			JSON_OBJECT('id',u.id,'name',u.name,'createTime',u.createAt,'avatar',u.avatar,'account',u.account,
			'fans_counts', (SELECT COUNT(*) FROM attention WHERE attention_uid = u.id),
			'attention_counts',(SELECT COUNT(*) FROM attention WHERE uid = u.id),
			'support_counts',(SELECT COUNT(*) FROM article_support WHERE article_uid = u.id)) AS user,
			JSON_OBJECT('id',c.id,'label',c.category_label,'name',c.category_name,'avatar',c.category_avatar,'account',c.article_count) AS category,
			(SELECT COUNT(*) FROM article_save WHERE article_id = a.id) AS save_counts,
			(SELECT COUNT(*) FROM article_support WHERE article_id = a.id) AS support_counts,
			(SELECT 
					JSON_ARRAYAGG(
							JSON_OBJECT(
									'id', ac.id,
									'content', ac.content,'commentId',ac.comment_id,
									'user', JSON_OBJECT('id', u.id, 'name', u.name,'avatar',u.avatar,'account',u.account),
									'createAt', ac.createAt,
									'updateAt', ac.updateAt
							)
					)
				FROM article_comment ac
				LEFT JOIN user u ON u.id = ac.user_id
				WHERE ac.article_id = a.id) AS comments
      FROM article a
      LEFT JOIN user u ON u.id = a.uid
			LEFT JOIN category c ON c.id = a.category_id
			WHERE a.is_delete = 0 AND a.audit_state = 2 AND a.id = ?
      `;
      const [result] = await connection.execute(statement2, [articleId]);

      return result;
    } catch (error) {
      console.log(error);
    }
  }

  // 获取热门文章
  async getHotArticleService(limit) {
    const statement = `
      SELECT 
      a.id id, a.title title,a.summary summary,a.body body, 
      a.type type,a.weight weight,a.view_counts view_counts,
      CASE
        WHEN a.cover3 IS NOT NULL AND a.cover3 != '' THEN JSON_ARRAY(a.cover1, a.cover2, a.cover3)
        WHEN a.cover2 IS NOT NULL AND a.cover2 != '' THEN JSON_ARRAY(a.cover1, a.cover2)
        WHEN a.cover1 IS NOT NULL AND a.cover1 != '' THEN JSON_ARRAY(a.cover1)
        ELSE JSON_ARRAY()
      END AS covers,
      a.createAt createTime, a.updateAt updateTime,
      a.audit_reason audit_reason,a.audit_time audit_time,
      JSON_OBJECT('id',u.id,'name',u.name,'createTime',u.createAt,'avatar',u.avatar,'account',u.account,
      'fans_counts', (SELECT COUNT(*) FROM attention WHERE attention_uid = u.id),
      'attention_counts',(SELECT COUNT(*) FROM attention WHERE uid = u.id),
      'support_counts',(SELECT COUNT(*) FROM article_support WHERE article_uid = u.id)) AS user,
      JSON_OBJECT('id',c.id,'label',c.category_label,'name',c.category_name,'avatar',c.category_avatar,'account',c.article_count) AS category,
      (SELECT COUNT(*) FROM article_save WHERE article_id = a.id) AS save_counts,
      (SELECT COUNT(*) FROM article_support WHERE article_id = a.id) AS support_counts,
      (SELECT 
        JSON_ARRAYAGG(
            JSON_OBJECT(
                'id', ac.id,
                'content', ac.content,'commentId',ac.comment_id,
                'user', JSON_OBJECT('id', u.id, 'name', u.name,'avatar',u.avatar,'account',u.account),
                'createAt', ac.createAt,
                'updateAt', ac.updateAt
            )
        )
      FROM article_comment ac
      LEFT JOIN user u ON u.id = ac.user_id
      WHERE ac.article_id = a.id) AS comments
      FROM article a
      LEFT JOIN user u ON u.id = a.uid
      LEFT JOIN category c ON c.id = a.category_id
      WHERE a.is_delete = 0 AND a.audit_state = 2
      ORDER BY a.view_counts DESC
      LIMIT ? 
    `;
    try {
      const [result] = await connection.execute(statement, [limit]);
      return result;
    } catch (error) {
      console.log(error);
    }
  }

  // 获取随机文章
  async getRandomArticleService(limit) {
    const statement = `
      SELECT 
      a.id id, a.title title,a.summary summary,a.body body, 
      a.type type,a.weight weight,a.view_counts view_counts,
      CASE
        WHEN a.cover3 IS NOT NULL AND a.cover3 != '' THEN JSON_ARRAY(a.cover1, a.cover2, a.cover3)
        WHEN a.cover2 IS NOT NULL AND a.cover2 != '' THEN JSON_ARRAY(a.cover1, a.cover2)
        WHEN a.cover1 IS NOT NULL AND a.cover1 != '' THEN JSON_ARRAY(a.cover1)
        ELSE JSON_ARRAY()
      END AS covers,
      a.createAt createTime, a.updateAt updateTime,
      a.audit_reason audit_reason,a.audit_time audit_time,
      JSON_OBJECT('id',u.id,'name',u.name,'createTime',u.createAt,'avatar',u.avatar,'account',u.account,
      'fans_counts', (SELECT COUNT(*) FROM attention WHERE attention_uid = u.id),
      'attention_counts',(SELECT COUNT(*) FROM attention WHERE uid = u.id),
      'support_counts',(SELECT COUNT(*) FROM article_support WHERE article_uid = u.id)) AS user,
      JSON_OBJECT('id',c.id,'label',c.category_label,'name',c.category_name,'avatar',c.category_avatar,'account',c.article_count) AS category,
      (SELECT COUNT(*) FROM article_save WHERE article_id = a.id) AS save_counts,
      (SELECT COUNT(*) FROM article_support WHERE article_id = a.id) AS support_counts,
      (SELECT 
        JSON_ARRAYAGG(
            JSON_OBJECT(
                'id', ac.id,
                'content', ac.content,'commentId',ac.comment_id,
                'user', JSON_OBJECT('id', u.id, 'name', u.name,'avatar',u.avatar,'account',u.account),
                'createAt', ac.createAt,
                'updateAt', ac.updateAt
            )
        )
      FROM article_comment ac
      LEFT JOIN user u ON u.id = ac.user_id
      WHERE ac.article_id = a.id) AS comments
      FROM article a
      LEFT JOIN user u ON u.id = a.uid
      LEFT JOIN category c ON c.id = a.category_id
      WHERE a.is_delete = 0 AND a.audit_state = 2
      ORDER BY RAND() 
      LIMIT ? 
    `;
    try {
      const [result] = await connection.execute(statement, [limit]);
      return result;
    } catch (error) {
      console.log(error);
    }
  }
}

module.exports = new articleService();
